﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;


using System.IO;
using System.Data;
using System.Data.OleDb;
using System.Data.SqlClient;


namespace Trade.DBUtility.DataTableName
{
    public class CustomizableDesignBLL
    {
        #region 得到数据集数据
        public static DataSet GetData(string strSelectSQL)
        {
            try
            {
                DataTableName dtn = new DataTableName();
                dtn.strSelectSQL = strSelectSQL;
                dtn.strTableName = "Table1";
                return GetData(dtn, false);
            }
            catch (Exception ex)
            {
                throw (ex);
            }
        }
        public static DataSet GetData(string strSelectSQL, string strTableName)
        {
            try
            {
                DataTableName dtn = new DataTableName();
                dtn.strSelectSQL = strSelectSQL;
                dtn.strTableName = strTableName;
                return GetData(dtn, false);
            }
            catch (Exception ex)
            {
                throw (ex);
            }
        }
        public static DataSet GetData(string strSelectSQL, string strTableName, bool bNeedSchema)
        {
            try
            {
                DataTableName dtn = new DataTableName();
                dtn.strSelectSQL = strSelectSQL;
                dtn.strTableName = strTableName;
                return GetData(dtn, bNeedSchema);
            }
            catch (Exception ex)
            {
                throw (ex);
            }
        }
        public static DataSet GetData(List<string> strSelectSQLList)
        {
            try
            {
                List<DataTableName> dtnList = new List<DataTableName>();
                for (int i = 0; i < strSelectSQLList.Count; i++)
                {
                    DataTableName dtn = new DataTableName();
                    dtn.strSelectSQL = strSelectSQLList[i];
                    dtn.strTableName = "Table" + i.ToString();
                    dtnList.Add(dtn);
                }
                return GetData(dtnList, false);
            }
            catch (Exception ex)
            {
                throw (ex);
            }
        }
        public static DataSet GetData(List<string> strSelectSQLList, List<string> strTableNameList)
        {
            try
            {
                List<DataTableName> dtnList = new List<DataTableName>();
                for (int i = 0; i < strSelectSQLList.Count; i++)
                {
                    DataTableName dtn = new DataTableName();
                    dtn.strSelectSQL = strSelectSQLList[i];
                    dtn.strTableName = strTableNameList[i];
                    dtnList.Add(dtn);
                }
                return GetData(dtnList, false);
            }
            catch (Exception ex)
            {
                throw (ex);
            }
        }
        public static DataSet GetData(List<string> strSelectSQLList, List<string> strTableNameList, bool bNeedSchema)
        {
            try
            {
                List<DataTableName> dtnList = new List<DataTableName>();
                for (int i = 0; i < strSelectSQLList.Count; i++)
                {
                    DataTableName dtn = new DataTableName();
                    dtn.strSelectSQL = strSelectSQLList[i];
                    dtn.strTableName = strTableNameList[i];
                    dtnList.Add(dtn);
                }
                return GetData(dtnList, bNeedSchema);
            }
            catch (Exception ex)
            {
                throw (ex);
            }
        }
        public static DataSet GetData(DataTableName DataTablename, bool bNeedSchema)
        {
            try
            {
                List<string> strSqlList = new List<string>();
                if (DataTablename.SqlSelectParameterList != null)
                {
                    string strSelectSQL = DataTablename.strSelectSQL;
                    for (int j = 0; j < DataTablename.SqlSelectParameterList.Count; j++)
                    {
                        SqlParameter sqlParam = DataTablename.SqlSelectParameterList[j];
                        strSelectSQL = strSelectSQL + "\r\n@" + sqlParam.ParameterName + "   " + sqlParam.Value.ToString();
                    }
                    strSqlList.Add(strSelectSQL);
                }
                DataTablename = SetSerSqlSelectParameter(DataTablename);
                return CustomizableDesignDAL.GetDataToDS(DataTablename, bNeedSchema);
            }
            catch (Exception e)
            {
                throw (e);
            }
        }
        public static DataSet GetData(List<DataTableName> DataTableNameList, bool bNeedSchema)
        {
            try
            {

                List<string> strSqlList = new List<string>();
                for (int i = 0; i < DataTableNameList.Count; i++)
                {
                    string strSelectSQL = DataTableNameList[i].strSelectSQL;
                    if (DataTableNameList[i].SqlSelectParameterList != null)
                    {
                        for (int j = 0; j < DataTableNameList[i].SqlSelectParameterList.Count; j++)
                        {
                            SqlParameter sqlParam = DataTableNameList[i].SqlSelectParameterList[j];
                            strSelectSQL = strSelectSQL + "\r\n@" + sqlParam.ParameterName + "   " + sqlParam.Value.ToString();
                        }
                    }
                    strSqlList.Add(strSelectSQL);
                }
                DataTableNameList = SetSerSqlSelectParameterList(DataTableNameList);
                return CustomizableDesignDAL.GetDataToDS(DataTableNameList, bNeedSchema);
            }
            catch (Exception e)
            {
                throw (e);
            }
        }
        #endregion
        public static void SaveData(DataSet dsData, DataTableName DataTablename)
        {
            try
            {
                DataTablename = SetSerSqlSelectParameter(DataTablename);
                CustomizableDesignDAL.SaveDataFromDS(dsData, DataTablename);
            }
            catch (Exception e)
            {
                throw (e);
            }
        }
        public static void SaveData(DataSet dsData, List<DataTableName> DataTableNameList)
        {
            try
            {
                DataTableNameList = SetSerSqlSelectParameterList(DataTableNameList);
                CustomizableDesignDAL.SaveDataFromDS(dsData, DataTableNameList);
            }
            catch (Exception e)
            {
                throw (e);
            }
        }
        public static bool IsExist(DataTableName dataTableName)
        {
            try
            {
                dataTableName = SetSerSqlSelectParameter(dataTableName);
                return CustomizableDesignDAL.IsExist(dataTableName);
            }
            catch (Exception e)
            {
                throw (e);
            }
        }
        public static bool IsExist(string strIsExistsSQL)
        {
            try
            {
                DataTableName dtn = new DataTableName();
                dtn.strIsExistsSQL = strIsExistsSQL;
                return IsExist(dtn);
            }
            catch (Exception ex)
            {
                throw (ex);
            }
        }
        public static void ExecSQL(DataTableName dtn)
        {
            try
            {
                List<DataTableName> dtnList = new List<DataTableName>();
                dtnList.Add(dtn);
                ExecSQL(dtnList);
            }
            catch (Exception ex)
            {
                throw (ex);
            }
        }
        public static void ExecSQL(string strExecSQL)
        {
            try
            {
                List<DataTableName> dtnList = new List<DataTableName>();
                DataTableName dtn = new DataTableName();
                dtn.strExecSQL = strExecSQL;
                dtnList.Add(dtn);
                ExecSQL(dtnList);
            }
            catch (Exception ex)
            {
                throw (ex);
            }
        }
        public static void ExecSQL(List<string> strExecSQLList)
        {
            try
            {
                List<DataTableName> dtnList = new List<DataTableName>();
                for (int i = 0; i < strExecSQLList.Count; i++)
                {
                    DataTableName dtn = new DataTableName();
                    dtn.strExecSQL = strExecSQLList[i];
                    dtnList.Add(dtn);
                }
                ExecSQL(dtnList);
            }
            catch (Exception ex)
            {
                throw (ex);
            }
        }
        public static void ExecSQL(List<DataTableName> DataTableNameList)
        {
            try
            {
                DataTableNameList = SetSerSqlSelectParameterList(DataTableNameList);
                CustomizableDesignDAL.ExecSQL(DataTableNameList);
            }
            catch (Exception e)
            {
                throw (e);
            }
        }
        public static DataSet GetDataSetReportFromSQL(string strSQL)
        {
            try
            {
                return CustomizableDesignDAL.GetDataSetReportFromSQLToDS(strSQL);
            }
            catch (Exception e)
            {
                throw (e);
            }
        }
        //
        private static List<DataTableName> SetSerSqlSelectParameterList(List<DataTableName> DataTableNameList)
        {
            List<DataTableName> DataTableNameListReturn = new List<DataTableName>();
            foreach (DataTableName dtn in DataTableNameList)
            {
                DataTableName dtnreturn = new DataTableName();
                dtnreturn = SetSerSqlSelectParameter(dtn);
                DataTableNameListReturn.Add(dtnreturn);
            }
            return DataTableNameListReturn;
        }
        private static DataTableName SetSerSqlSelectParameter(DataTableName DataTablename)
        {
            DataTableName DataTableNameReturn = new DataTableName();
            DataTableNameReturn.strTableName = DataTablename.strTableName;
            DataTableNameReturn.strSelectSQL = DataTablename.strSelectSQL;
            DataTableNameReturn.strExecSQL = DataTablename.strExecSQL;
            DataTableNameReturn.strIsExistsSQL = DataTablename.strIsExistsSQL;
            DataTableNameReturn.strSaveSQL = DataTablename.strSaveSQL;

            if (DataTablename.SqlSelectParameterList != null)
            {
                foreach (SqlParameter pa in DataTablename.SqlSelectParameterList)
                {
                    DataTableName.SerSqlParameter serparam = new DataTableName.SerSqlParameter(pa);
                    DataTableNameReturn.SerSqlSelectParameterList.Add(serparam);
                }
            }
            //
            if (DataTablename.SqlExecParameterList != null)
            {
                foreach (SqlParameter pa in DataTablename.SqlExecParameterList)
                {
                    DataTableName.SerSqlParameter serparam = new DataTableName.SerSqlParameter(pa);
                    DataTableNameReturn.SerSqlExecParameterList.Add(serparam);
                }
            }
            //
            if (DataTablename.SqlIsExistsParameterList != null)
            {
                foreach (SqlParameter pa in DataTablename.SqlIsExistsParameterList)
                {
                    DataTableName.SerSqlParameter serparam = new DataTableName.SerSqlParameter(pa);
                    DataTableNameReturn.SerSqlIsExistsParameterList.Add(serparam);
                }
            }
            //
            if (DataTablename.ExecAfterSQLList != null)
            {
                foreach (DataTableName.ExecAfterSQL execsql in DataTablename.ExecAfterSQLList)
                {
                    DataTableName.ExecAfterSQL execsql1 = new DataTableName.ExecAfterSQL();
                    execsql1.strSQL = execsql.strSQL;
                    //
                    if (execsql.SqlParameterList != null)
                    {
                        foreach (SqlParameter pa in execsql.SqlParameterList)
                        {
                            DataTableName.SerSqlParameter serparam = new DataTableName.SerSqlParameter(pa);
                            execsql1.SerSqlParameterList.Add(serparam);
                        }
                    }
                    DataTableNameReturn.ExecAfterSQLList.Add(execsql1);
                }
            }
            //
            if (DataTablename.SaveAfterSQLList != null)
            {
                foreach (DataTableName.SaveAfterSQL execsql in DataTablename.SaveAfterSQLList)
                {
                    DataTableName.SaveAfterSQL execsql1 = new DataTableName.SaveAfterSQL();
                    execsql1.strSQL = execsql.strSQL;
                    //
                    if (execsql.SqlParameterList != null)
                    {
                        foreach (SqlParameter pa in execsql.SqlParameterList)
                        {
                            DataTableName.SerSqlParameter serparam = new DataTableName.SerSqlParameter(pa);
                            execsql1.SerSqlParameterList.Add(serparam);
                        }
                    }
                    DataTableNameReturn.SaveAfterSQLList.Add(execsql1);
                }
            }
            return DataTableNameReturn;
        }
        private static string ERPSQLLogFile = string.Empty;
        public static void WriteSQLLog(List<string> strSqlList)
        {
            string strPath;
            StreamWriter swLog;
            StringBuilder strbContent = new StringBuilder();

            #region 生成SQL日志路径
            if (ERPSQLLogFile == string.Empty)
            {
                strPath = AppDomain.CurrentDomain.SetupInformation.ApplicationBase;
                if (!strPath.EndsWith("\\"))
                    strPath += "\\";
                strPath += "ERPSQL.log";
                ERPSQLLogFile = strPath;
            }
            #endregion
            #region 生成错误内容
            strbContent.Append("发生时间:" + System.DateTime.Now.ToString("F") + "\r\n");
            for (int i = 0; i < strSqlList.Count; i++)
            {
                strbContent.Append("SQL语句:" + strSqlList[i] + "\r\n\r\n");
            }
            strbContent.Append("\r\n---------------------------------------------------\r\n");
            #endregion
            try
            {
                if (File.Exists(ERPSQLLogFile))
                {
                    using (swLog = File.AppendText(ERPSQLLogFile))
                    {
                        swLog.Write(strbContent.ToString());	//写入文件内容
                        swLog.Close();	//保存文件
                    }
                }
                else
                {
                    using (swLog = File.CreateText(ERPSQLLogFile))
                    {
                        swLog.Write(strbContent.ToString());		//写入文件内容
                        swLog.Close(); //保存文件
                    }
                }
            }
            catch
            {

            }
        }
        #region 模块自动编码
        /// <summary>
        /// 得到自动编码
        /// </summary>
        /// <param name="strModuleName">模块</param>
        /// <param name="dsData">模块数据</param>
        /// <returns></returns>
        public static string GetModuleAutoCode(string strModuleName, DataSet ds, string strTableName, string strPKCodeName)
        {
            DataSet dsAutoCodeSet = new DataSet("AutoCodeSet");            //
            DataTableName DataTableNamevar = new DataTableName();
            DataTableNamevar.strSelectSQL = "SELECT * FROM T_AutoCodeSet where ModuleName =N'" + strModuleName + "' AND ISAutoCode = 1";
            DataTableNamevar.strTableName = "T_AutoCodeSet";
            try
            {
                dsAutoCodeSet = GetData(DataTableNamevar, false);
            }
            catch (Exception e)
            {
                throw (e);
            }
            if (dsAutoCodeSet.Tables.Count < 1 || dsAutoCodeSet.Tables[0].DefaultView.Count < 1)
                return string.Empty;
            //解析
            bool bISNewSuffix = dsAutoCodeSet.Tables[0].Rows[0]["ISNewSuffix"] == DBNull.Value ? false : (bool)(dsAutoCodeSet.Tables[0].Rows[0]["ISNewSuffix"]);
            //自动编码的长度
            int iSerialCount = dsAutoCodeSet.Tables[0].Rows[0]["SerialLen"] == DBNull.Value ? 4 : (int)(dsAutoCodeSet.Tables[0].Rows[0]["SerialLen"]);
            //递归得到固定字串
            string strMasterCode = GetMasterOfAutoCode(dsAutoCodeSet.Tables[0].Rows[0]["Prefix"].ToString().Trim(), ds);
            string strMasterCode1 = GetMasterOfAutoCode(dsAutoCodeSet.Tables[0].Rows[0]["Suffix"].ToString().Trim(), ds);
            string strSerialCode = strMasterCode;
            for (int i = 1; i <= iSerialCount; i++)
            {
                strSerialCode += "[0-9]";
            }
            int iMasterCodeLength = strMasterCode.Length + iSerialCount;
            string strSqlMaxCode = string.Empty;
            if (bISNewSuffix)
                strSqlMaxCode = "SELECT MAX(SubString(" + strPKCodeName + ",1," + iMasterCodeLength + ")) MaxCode FROM " + strTableName + " WHERE " + strPKCodeName + " LIKE '" + strSerialCode + strMasterCode1 + "'";
            else
                strSqlMaxCode = "SELECT MAX(SubString(" + strPKCodeName + ",1," + iMasterCodeLength + ")) MaxCode FROM " + strTableName + " WHERE " + strPKCodeName + " LIKE '" + strSerialCode + "%'";

            //if (ds.Tables[0].Columns.Contains("ISDel"))
            strSqlMaxCode = strSqlMaxCode + " AND ISDel=0 ";

            string strMaxCode;
            //
            DataTableNamevar = new DataTableName();
            DataTableNamevar.strSelectSQL = strSqlMaxCode;
            DataTableNamevar.strTableName = "T_AutoCodeSet";
            DataSet dstemp = new DataSet();
            try
            {
                dstemp = GetData(DataTableNamevar, false);
            }
            catch (Exception ex)
            {
                throw (ex);
                //strMaxCode = string.Empty;
            }
            if (dstemp != null && dstemp.Tables.Count > 0 && dstemp.Tables[0].DefaultView.Count > 0)
                strMaxCode = dstemp.Tables[0].DefaultView[0]["MaxCode"].ToString();
            else
                strMaxCode = "";
            int iAutoIndex;
            if (strMaxCode == string.Empty)
                iAutoIndex = 1;
            else
                iAutoIndex = int.Parse(strMaxCode.Substring(strMasterCode.Length)) + 1;
            for (int i = 0; i < iSerialCount - iAutoIndex.ToString().Length; i++)
            {
                strMasterCode += "0";
            }
            strMasterCode = strMasterCode + iAutoIndex.ToString() + strMasterCode1;
            return strMasterCode;
        }

        public static string GetMasterOfAutoCode(string strAutoCode, DataSet ds)
        {
            string strFirstCode = string.Empty;
            string strLastCode = string.Empty;
            if (strAutoCode.IndexOf("[") >= 0 && strAutoCode.IndexOf("]") >= 0)
            {
                strFirstCode = strAutoCode.Substring(0, strAutoCode.IndexOf("["));
                if (strAutoCode.Length > strAutoCode.IndexOf("]") + 1)
                    strLastCode = strAutoCode.Substring(strAutoCode.IndexOf("]") + 1);
                strAutoCode = strAutoCode.Substring(strAutoCode.IndexOf("["), strAutoCode.IndexOf("]") - strAutoCode.IndexOf("[") + 1);
                #region 分解替换
                if (strAutoCode == "[yyyy]" || strAutoCode == "[YYYY]") //4位年
                {
                    strAutoCode = DateTime.Now.ToString("yyyy");
                }
                else if (strAutoCode == "[yy]" || strAutoCode == "[YY]") //2位年
                {
                    strAutoCode = DateTime.Now.ToString("yy");
                }
                else if (strAutoCode == "[mm]" || strAutoCode == "[MM]") //2位月
                {
                    strAutoCode = DateTime.Now.ToString("MM");
                }
                else if (strAutoCode == "[dd]" || strAutoCode == "[DD]") //2位日
                {
                    strAutoCode = DateTime.Now.ToString("dd");
                }
                else
                {
                    string strTableName;
                    string strColumnName;
                    string strFilter;
                    try
                    {
                        strTableName = strAutoCode.Substring(1, strAutoCode.IndexOf(".") - 1);
                        strColumnName = strAutoCode.Substring(strAutoCode.IndexOf(".") + 1, strAutoCode.Length - strAutoCode.IndexOf(".") - 2);
                        DataRow[] drSelectRows = ds.Tables[strTableName].Select(strFilter = "" + strColumnName + " <> ''");
                        if (drSelectRows.Length == 0)
                            strAutoCode = "";
                        else
                            strAutoCode = drSelectRows[0][strColumnName].ToString().Trim();
                    }
                    catch (Exception)
                    {
                        strAutoCode = string.Empty;
                    }
                }
                #endregion
                return GetMasterOfAutoCode(strFirstCode + strAutoCode + strLastCode, ds);
            }
            else
                return strAutoCode;
        }
        /// <summary>
        /// 传入前缀号获取加上对应的流水号的编码
        /// </summary>
        /// <param name="strMasterCode">前缀号码</param>
        /// <param name="iSerialCount">流水号长度</param>
        /// <param name="strTableName">对应的表名</param>
        /// <param name="strPKCodeName">字段名称</param>
        /// <returns>前缀加上流水号返回值</returns>
        public static string GetLiushuiCode(string strMasterCode, int iSerialCount, string strTableName, string strPKCodeName)
        {
            int iMasterCodeLength = strMasterCode.Length+1;
            string strSqlMaxCode = "SELECT MAX(SubString(" + strPKCodeName + "," + iMasterCodeLength + "," + iSerialCount + ")) MaxCode FROM " + strTableName + " WHERE " + strPKCodeName + " LIKE '" + strMasterCode + "%'";
            strSqlMaxCode = strSqlMaxCode + " AND ISDel=0 ";

            DataTableName DataTableNamevar = new DataTableName();
            DataTableNamevar.strSelectSQL = strSqlMaxCode;
            DataTableNamevar.strTableName = "T_MaxCode";
            DataSet dstemp = new DataSet();
            try
            {
                dstemp = GetData(DataTableNamevar, false);
            }
            catch (Exception ex)
            {
                throw (ex);
                //strMaxCode = string.Empty;
            }
            string strMaxCode;
            if (dstemp != null && dstemp.Tables.Count > 0 && dstemp.Tables[0].DefaultView.Count > 0)
                strMaxCode = dstemp.Tables[0].DefaultView[0]["MaxCode"].ToString();
            else
                strMaxCode = "";
            int iAutoIndex;
            if (strMaxCode == string.Empty)
                iAutoIndex = 1;
            else
                iAutoIndex = int.Parse(strMaxCode) + 1;
            for (int i = 0; i < iSerialCount - iAutoIndex.ToString().Length; i++)
            {
                strMasterCode += "0";
            }
            //
            strMasterCode = strMasterCode + iAutoIndex.ToString();
            return strMasterCode;
        }

        #endregion
    }
}
